﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using mybolg.Models;

namespace mybolg.DAL
{

    
    /// <summary>
    /// 构造传入你的tableinfo将得到你想要的对象，并可获取table的属性等信息（参数属性需全部声明）
    /// </summary>
    public class myTable
    {
     private  Models.BlogEntities11 DBblog = new Models.BlogEntities11();

        //tbinfo
        private TableInfo TbInfo { get; set; }
        //------------响应信息
        //结果信息
        private  TableResult TbResult { get; set; }

        private List<string> delids = new List<string>();
        //构造函数
        public myTable(TableInfo TbInfo) {

            //对象初始化
           this.TbResult = new TableResult();
            this.TbInfo = TbInfo;

            if (!TbInfo.IsRTData)
            {
                delids = TbInfo.IndexDelIds;

            }
            else {
                setTbinfoIndexDelIds();
            }
            setBaseTableQueryString();
        }

      
        public TableResult getTableResult<T>()
        {
            setFilterTableQueryString();
            TbResult.Count= getTableCount(TbResult.QueryString);
            setPagingTableQueryString();
            TbResult.DbList(DBblog.Database.SqlQuery<T>(TbResult.QueryString).ToList());
            return TbResult;
        }
        public TableInfo getTableInfo()
        {
            return TbInfo;
        }





        /// <summary>
        /// 得到已删除数据组
        /// </summary>
        private void setTbinfoIndexDelIds()
        {
          TbResult.DelIdsSqlString = "select id from "+ TbInfo.TableName + " where Time<='"+TbInfo.DataTime.ToString()+"' and IsDelete=1";
            TbInfo.IndexDelIds = DBblog.Database.SqlQuery<string>(TbResult.DelIdsSqlString).ToList<string>();
            delids = TbInfo.IndexDelIds;
        }
        /// <summary>
        /// 得到当前构造完生成的表-查询串，前提生成DelIds
        /// </summary>
        private void setBaseTableQueryString()
        {
            TbResult.QueryString = "select * from " + TbInfo.TableName + " where Time<='" + TbInfo.DataTime.ToString() + "' ";
            if (TbInfo.DateType == 0)
            {
                foreach (var item in delids)
                {
                    TbResult.QueryString += "and id!='" + item + "' ";
                }
            }
            else {
                foreach (var item in delids)
                {
                    TbResult.QueryString += "and id='" + item + "' ";
                }

            }
            
        }
        /// <summary>
        /// 得到筛选后结果-前提为构造完成-即生成基础表
        /// </summary>
        private void setFilterTableQueryString()
        {
            TbResult.QueryString = "select * from (" + TbResult.QueryString + ") as bs where 1=1 ";
            foreach (var item in TbInfo.Filter)
            {
                TbResult.QueryString += "and " + item.Key + "='" + item.Value + "' ";
            }
        }
     

        /// <summary>
        /// 得到分页并带有排序--前提生成筛选后结果
        /// </summary>
        private void setPagingTableQueryString()
        {
            int maxCount = TbResult.Count;
            int maxNumber = maxCount / TbInfo.pagingCount;
            int lastCount = maxCount % TbInfo.pagingCount;
            if (lastCount > 0) { maxNumber += 1; }
            string querystr = "select Top " + TbInfo.pagingCount + " * from (select top " + TbInfo.pagingNumber * TbInfo.pagingCount + " * from (" + TbResult.QueryString + ") as tb order by "+ TbInfo.sortName + " "+ (TbInfo.sortType==""?"": "desc") + ")ss  order by " + TbInfo.sortName +" "+ (TbInfo.sortType == "" ? "desc" : "");
            if (lastCount > 0 && maxNumber == TbInfo.pagingNumber)
            {
                querystr = "select Top " + lastCount + " * from (select top " + TbInfo.pagingNumber * TbInfo.pagingCount + " * from (" + TbResult.QueryString + ") as tb order by " + TbInfo.sortName + " " + (TbInfo.sortType == "" ? "" : "desc") + ")ss  order by " + TbInfo.sortName + " " + (TbInfo.sortType == "" ? "desc" : "");
             }
            querystr = "select * from ("+ querystr + ") as ssP  order by " + TbInfo.sortName + " " + TbInfo.sortType ;
            TbResult.QueryString = querystr;
        }

        private int getTableCount(string FilterQueryString)
        {
            string Querystr = "select COUNT(*) as count  from ("+ FilterQueryString + ") as filt";
            var Count = DBblog.Database.SqlQuery<int>(Querystr).ToList()[0];
            return Count;
        }



    }
    public class TableInfo {
        //TableName
        public string TableName { get; set; }
        //数据时间
        public DateTime DataTime { get; set; }
        //数据时间的删除记录id
        public List<string> IndexDelIds { get; set; }
        //获取数据类型：已删除1 正常0
        public int DateType { get; set; }
        //是否实时数据
        public bool IsRTData { get; set; }
        //排序字段
        public string sortName { get; set; }
        //排序类型
        public string sortType { get; set; }
        //筛选信息串
        public Dictionary<string,string> Filter { get; set; }
        //分页条数
        public int pagingCount { get; set; }
        //分页页码
        public int pagingNumber { get; set; }
        public TableInfo() {
            DataTime = new DateTime();
            IndexDelIds = new List<string>();
            Filter = new Dictionary<string, string>();
        }
    }
    public class TableResult {
        //记录总条数
        public int Count { get; set; }
        private object dblist { get; set; }
        public string QueryString { get; set; }
        public string DelIdsSqlString { get; set; }
        public void DbList(object dblst)
        {
            dblist = dblst;
        }
        public List<T> Dblist<T>()
        {
            return dblist as List<T>;
        }

    }




}